''' 滴滴打灰后端应用服务 数据库操作API （UMS管理） '''
import sys,traceback,logging,datetime
import pymysql
from dbutils.pooled_db import PooledDB 
from utils.db_config import mysqlInfo
from utils.tokenutil import TokenUtil
from utils.didiutils import Encrypt,generate_verification_code,getOutTradeNo


logger = logging.getLogger('umsservice')

# 创建数据库连接
def connect_db():
    pool=PooledDB(creator=pymysql,mincached=2, maxcached=5,maxshared=3, maxconnections=6, blocking=True,host=mysqlInfo['host'],port=mysqlInfo['port'],
        user=mysqlInfo['user'],password=mysqlInfo['password'],database=mysqlInfo['database'],charset=mysqlInfo['charset'])
    return pool.connection()


# 手机号验证码登录
def umsmemberLoginForCode(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    rowdict = {}
    try:
        sql = " select member_id AS 'memberId',  member_sn AS 'memberSn',company AS 'company', company_id AS 'companyId', post_name AS 'postName', name AS 'name',nick_name AS 'nickName', " \
              "   code AS 'code',phone AS 'phone', password AS 'password', salt AS 'salt', " \
              "   avatar AS 'avatar', sex AS 'sex', id_number AS 'idNumber', member_role AS 'memberRole', vip_grade AS 'vipGrade', vip_end_date AS 'vipEndDate', " \
              "   signs AS 'signs', source_type AS 'sourceType', recommender_id AS 'recommenderId', recommender_number AS 'recommenderNumber', " \
              "   teacher_number AS 'teacherNumber',student_number AS 'studentNumber',verify_time AS 'verifyTime', " \
              "   bind_teacher_number AS 'bindTeacherNumber',bind_student_number AS 'bindStudentNumber', " \
              "   remark AS 'remark',binded AS 'binded',binded_teacher_id AS 'bindedTeacherId',binded_time AS 'bindedTime',recommend_type AS 'recommendType', " \
              "   status AS 'status', create_time AS 'createTime', update_time AS 'updateTime', login_time AS 'loginTime', if_del AS 'ifDel', delete_time AS 'deleteTime', open_id AS 'openId' " \
              " from ums_member where if_del = 0 and phone = %s " 
        cur.execute(sql,(param['phone']))
        for row in cur.fetchall():
            rowdict['memberId'] = row[0]
            rowdict['memberSn'] = row[1]
            rowdict['company'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['postName'] = row[4]
            rowdict['name'] = row[5]
            rowdict['nickName'] = row[6]
            rowdict['code'] = row[7]
            rowdict['phone'] = row[8]
            rowdict['password'] = row[9]
            rowdict['salt'] = row[10]
            
            rowdict['avatar'] = row[11]
            rowdict['sex'] = row[12]
            rowdict['idNumber'] = row[13]
            rowdict['memberRole'] = row[14]
            rowdict['vipGrade'] = row[15]
            rowdict['vipEndDate'] = row[16]
            rowdict['signs'] = row[17]
            rowdict['sourceType'] = row[18]
            
            rowdict['recommenderId'] = row[19]
            rowdict['recommenderNumber'] = row[20]
            rowdict['teacherNumber'] = row[21]
            rowdict['studentNumber'] = row[22]
            rowdict['verifyTime'] = row[23]
            rowdict['bindTeacherNumber'] = row[24]
            rowdict['bindStudentNumber'] = row[25]

            rowdict['remark'] = row[26]
            rowdict['binded'] = row[27]

            rowdict['bindedTeacherId'] = row[28]
            rowdict['bindedTime'] = row[29]
            rowdict['recommendType'] = row[30]
            rowdict['status'] = row[31]
            rowdict['createTime'] = row[32]
            rowdict['updateTime'] = row[33]
            rowdict['loginTime'] = row[34]
            rowdict['ifDel'] = row[35]

            rowdict['deleteTime'] = row[36]
            rowdict['openId'] = row[37]

        if rowdict:
            payload = TokenUtil.getPayload(rowdict['memberId'], param['phone'])
            token = TokenUtil.encode(payload)
            result['token'] = token
            result['currMember'] = rowdict
            result['expire'] = payload['exp']
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取用户资料
def umsgetMember(param):
    conn = connect_db()
    cur = conn.cursor()
    results = {}
    results['code'] = '200'
    rowdict = {}
    try:
        sql = " select member_id AS 'memberId',  member_sn AS 'memberSn',company AS 'company', company_id AS 'companyId', post_name AS 'postName', name AS 'name',nick_name AS 'nickName', " \
              "   code AS 'code',phone AS 'phone', password AS 'password', salt AS 'salt', " \
              "   avatar AS 'avatar', sex AS 'sex', id_number AS 'idNumber', member_role AS 'memberRole', vip_grade AS 'vipGrade', vip_end_date AS 'vipEndDate', " \
              "   signs AS 'signs', source_type AS 'sourceType', recommender_id AS 'recommenderId', recommender_number AS 'recommenderNumber', " \
              "   teacher_number AS 'teacherNumber',student_number AS 'studentNumber',verify_time AS 'verifyTime', " \
              "   bind_teacher_number AS 'bindTeacherNumber',bind_student_number AS 'bindStudentNumber', " \
              "   remark AS 'remark',binded AS 'binded',binded_teacher_id AS 'bindedTeacherId',binded_time AS 'bindedTime',recommend_type AS 'recommendType', " \
              "   status AS 'status', create_time AS 'createTime', update_time AS 'updateTime', login_time AS 'loginTime', if_del AS 'ifDel', delete_time AS 'deleteTime', open_id AS 'openId' " \
              " from ums_member where if_del = 0 and member_id = %s " 
        cur.execute(sql,(param['memberId']))
        for row in cur.fetchall():
            rowdict['memberId'] = row[0]
            rowdict['memberSn'] = row[1]
            rowdict['company'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['postName'] = row[4]
            rowdict['name'] = row[5]
            rowdict['nickName'] = row[6]
            rowdict['code'] = row[7]
            rowdict['phone'] = row[8]
            rowdict['password'] = row[9]
            rowdict['salt'] = row[10]
            
            rowdict['avatar'] = row[11]
            rowdict['sex'] = row[12]
            rowdict['idNumber'] = row[13]
            rowdict['memberRole'] = row[14]
            rowdict['vipGrade'] = row[15]
            rowdict['vipEndDate'] = row[16]
            rowdict['signs'] = row[17]
            rowdict['sourceType'] = row[18]
            
            rowdict['recommenderId'] = row[19]
            rowdict['recommenderNumber'] = row[20]
            rowdict['teacherNumber'] = row[21]
            rowdict['studentNumber'] = row[22]
            rowdict['verifyTime'] = row[23]
            rowdict['bindTeacherNumber'] = row[24]
            rowdict['bindStudentNumber'] = row[25]

            rowdict['remark'] = row[26]
            rowdict['binded'] = row[27]

            rowdict['bindedTeacherId'] = row[28]
            rowdict['bindedTime'] = row[29]
            rowdict['recommendType'] = row[30]
            rowdict['status'] = row[31]
            rowdict['createTime'] = row[32]
            rowdict['updateTime'] = row[33]
            rowdict['loginTime'] = row[34]
            rowdict['ifDel'] = row[35]

            rowdict['deleteTime'] = row[36]
            rowdict['openId'] = row[37]
            rowdict['companyAuth'] = 0
            rowdict['ifAddResume'] = False
        if rowdict:
            if rowdict['memberRole'] == 1 and rowdict['companyId'] > 0:
                sql = " select verify_status from rms_company where 1 = 1 and id = %s "
                cur.execute(sql,(rowdict['companyId']))
                for row in cur.fetchall():
                    rowdict['companyAuth'] = row[0]

            if rowdict['memberRole'] == 0:
                sql = " select id from wms_resume where 1 = 1 AND member_id = %s "
                cur.execute(sql,(param['memberId']))
                for row in cur.fetchall():            
                    rowdict['ifAddResume'] = True
            results['data'] = rowdict
            
    except :
        results['code'] = '500'
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return results

# 账号密码登录
def memberLogin(param):
    conn = connect_db()
    cur = conn.cursor()
    result = {}
    rowdict = {}
    try:
        sql = " select member_id AS 'memberId',  member_sn AS 'memberSn',company AS 'company', company_id AS 'companyId', post_name AS 'postName', name AS 'name',nick_name AS 'nickName', " \
              "   code AS 'code',phone AS 'phone', password AS 'password', salt AS 'salt', " \
              "   avatar AS 'avatar', sex AS 'sex', id_number AS 'idNumber', member_role AS 'memberRole', vip_grade AS 'vipGrade', vip_end_date AS 'vipEndDate', " \
              "   signs AS 'signs', source_type AS 'sourceType', recommender_id AS 'recommenderId', recommender_number AS 'recommenderNumber', " \
              "   teacher_number AS 'teacherNumber',student_number AS 'studentNumber',verify_time AS 'verifyTime', " \
              "   bind_teacher_number AS 'bindTeacherNumber',bind_student_number AS 'bindStudentNumber', " \
              "   remark AS 'remark',binded AS 'binded',binded_teacher_id AS 'bindedTeacherId',binded_time AS 'bindedTime',recommend_type AS 'recommendType', " \
              "   status AS 'status', create_time AS 'createTime', update_time AS 'updateTime', login_time AS 'loginTime', if_del AS 'ifDel', delete_time AS 'deleteTime', open_id AS 'openId' " \
              " from ums_member where if_del = 0 and phone = %s " 
        cur.execute(sql,(param['phone']))
        for row in cur.fetchall():
            rowdict['memberId'] = row[0]
            rowdict['memberSn'] = row[1]
            rowdict['company'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['postName'] = row[4]
            rowdict['name'] = row[5]
            rowdict['nickName'] = row[6]
            rowdict['code'] = row[7]
            rowdict['phone'] = row[8]
            rowdict['password'] = row[9]
            rowdict['salt'] = row[10]
            
            rowdict['avatar'] = row[11]
            rowdict['sex'] = row[12]
            rowdict['idNumber'] = row[13]
            rowdict['memberRole'] = row[14]
            rowdict['vipGrade'] = row[15]
            rowdict['vipEndDate'] = row[16]
            rowdict['signs'] = row[17]
            rowdict['sourceType'] = row[18]
            
            rowdict['recommenderId'] = row[19]
            rowdict['recommenderNumber'] = row[20]
            rowdict['teacherNumber'] = row[21]
            rowdict['studentNumber'] = row[22]
            rowdict['verifyTime'] = row[23]
            rowdict['bindTeacherNumber'] = row[24]
            rowdict['bindStudentNumber'] = row[25]

            rowdict['remark'] = row[26]
            rowdict['binded'] = row[27]

            rowdict['bindedTeacherId'] = row[28]
            rowdict['bindedTime'] = row[29]
            rowdict['recommendType'] = row[30]
            rowdict['status'] = row[31]
            rowdict['createTime'] = row[32]
            rowdict['updateTime'] = row[33]
            rowdict['loginTime'] = row[34]
            rowdict['ifDel'] = row[35]

            rowdict['deleteTime'] = row[36]
            rowdict['openId'] = row[37]

        if rowdict:
            if rowdict['salt'] is not None:
                enc = Encrypt(rowdict['salt'])
                j_pwd = enc.decrypt(rowdict['password'])
                if j_pwd != param['password']:
                    result['msg'] = '账号密码错误'
                else:
                    payload = TokenUtil.getPayload(rowdict['memberId'], param['phone'])
                    token = TokenUtil.encode(payload)
                    result['token'] = token
                    result['currMember'] = rowdict
                    result['expire'] = payload['exp']
                    result['msg'] = ''
            else:
               result['msg'] = '账号密码错误'
        else:
            # 不存在则自动注册
            param['code'] = generate_verification_code(6)
            salt = generate_verification_code(16)
            param['salt'] = salt
            enc = Encrypt(salt)
            pwd = enc.encrypt(param['password'])
            param['t_password'] = pwd
            param['memberSn'] = getOutTradeNo()
            param['name'] = param['phone']
            param['nickName'] = param['phone']    
            
            sql = "insert into ums_member ( member_sn, code, nick_name, name, phone, password, salt, member_role, create_time ) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cnt = cur.execute(sql,(param['memberSn'],param['code'],param['phone'],param['phone'],param['phone'],param['t_password'],param['salt'],param['memberrole'],datetime.datetime.now()))
            conn.commit()  
            sql = "select member_id from ums_member where if_del = 0 and phone = %s "
            cur.execute(sql,(param['phone']))
            for row in cur.fetchall():
                param['memberId'] = row[0]
                sql = "insert into ums_member_account ( member_id, create_time ) values (%s, %s)"
                cnt = cur.execute(sql,(row[0],datetime.datetime.now()))
            conn.commit()  
            payload = TokenUtil.getPayload(param['memberId'], param['phone'])
            token = TokenUtil.encode(payload)
            result['token'] = token
            result['currMember'] = param
            result['expire'] = payload['exp']
            result['msg'] = ''
            
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return result

# 获取用户资料
def getMemberBySn(param):
    conn = connect_db()
    cur = conn.cursor()
    rowdict = {}
    try:
        sql = " select member_id AS 'memberId',  member_sn AS 'memberSn',company AS 'company', company_id AS 'companyId', post_name AS 'postName', name AS 'name',nick_name AS 'nickName', " \
              "   code AS 'code',phone AS 'phone', password AS 'password', salt AS 'salt', " \
              "   avatar AS 'avatar', sex AS 'sex', id_number AS 'idNumber', member_role AS 'memberRole', vip_grade AS 'vipGrade', vip_end_date AS 'vipEndDate', " \
              "   signs AS 'signs', source_type AS 'sourceType', recommender_id AS 'recommenderId', recommender_number AS 'recommenderNumber', " \
              "   teacher_number AS 'teacherNumber',student_number AS 'studentNumber',verify_time AS 'verifyTime', " \
              "   bind_teacher_number AS 'bindTeacherNumber',bind_student_number AS 'bindStudentNumber', " \
              "   remark AS 'remark',binded AS 'binded',binded_teacher_id AS 'bindedTeacherId',binded_time AS 'bindedTime',recommend_type AS 'recommendType', " \
              "   status AS 'status', create_time AS 'createTime', update_time AS 'updateTime', login_time AS 'loginTime', if_del AS 'ifDel', delete_time AS 'deleteTime', open_id AS 'openId' " \
              " from ums_member where if_del = 0 and member_sn = %s " 
        cur.execute(sql,(param['memberSn']))
        for row in cur.fetchall():
            rowdict['memberId'] = row[0]
            rowdict['memberSn'] = row[1]
            rowdict['company'] = row[2]
            rowdict['companyId'] = row[3]
            rowdict['postName'] = row[4]
            rowdict['name'] = row[5]
            rowdict['nickName'] = row[6]
            rowdict['code'] = row[7]
            rowdict['phone'] = row[8]
            rowdict['password'] = row[9]
            rowdict['salt'] = row[10]
            rowdict['avatar'] = row[11]
            rowdict['sex'] = row[12]
            rowdict['idNumber'] = row[13]
            rowdict['memberRole'] = row[14]
            rowdict['vipGrade'] = row[15]
            rowdict['vipEndDate'] = row[16]
            rowdict['signs'] = row[17]
            rowdict['sourceType'] = row[18]
            rowdict['recommenderId'] = row[19]
            rowdict['recommenderNumber'] = row[20]
            rowdict['teacherNumber'] = row[21]
            rowdict['studentNumber'] = row[22]
            rowdict['verifyTime'] = row[23]
            rowdict['bindTeacherNumber'] = row[24]
            rowdict['bindStudentNumber'] = row[25]
            rowdict['remark'] = row[26]
            rowdict['binded'] = row[27]
            rowdict['bindedTeacherId'] = row[28]
            rowdict['bindedTime'] = row[29]
            rowdict['recommendType'] = row[30]
            rowdict['status'] = row[31]
            rowdict['createTime'] = row[32]
            rowdict['updateTime'] = row[33]
            rowdict['loginTime'] = row[34]
            rowdict['ifDel'] = row[35]
            rowdict['deleteTime'] = row[36]
            rowdict['openId'] = row[37]
            
    except :
        logger.error(sys.exc_info())
        logger.error(traceback.format_exc())
        conn.rollback()
    finally:
        cur.close()
        conn.close()
        return rowdict











































